package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Material;
import model.OrdenDeCompra;
import model.OrdenDeCompraDetalle;
import model.Proveedor;

public class OrdenDeCompraMapper implements DataMapper<OrdenDeCompra>{
	
	private static OrdenDeCompraMapper instance = null;

	private OrdenDeCompraMapper() {}
	
	public static OrdenDeCompraMapper getInstance() {
		if(instance == null) {
			instance = new OrdenDeCompraMapper();
		}
		
		return instance;
	}
	@Override
	public boolean insertar(OrdenDeCompra ordenCompra) {
		boolean result = false;
		Connection connection = PoolConnection.getPoolConnection().getConnection();
		
		try{
			String query = "insert into orden_compra (monto, proveedor_id) values (?,?)";
			PreparedStatement ps = null;
			ps = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			ps.setFloat(1, ordenCompra.getMonto());
			ps.setLong(2, ordenCompra.getProveedor().getProveedorId());
			ps.execute();
			
			ResultSet rs = ps.getGeneratedKeys();
			int ordenId = -1;
            if(rs.next()) {
                ordenId = rs.getInt(1);
                ordenCompra.setOrdenId(ordenId);
            }
			
			for(OrdenDeCompraDetalle detalle: ordenCompra.getItems()) {
				insertarDetallesOrden(connection, ordenId, detalle);
				MaterialMapper.getInstance().actualizarStock(detalle.getMaterial(), detalle.getCantidad());
			}
			
			PoolConnection.getPoolConnection().releaseConnection(connection);
			result = true;
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar la Orden de Compra: " + e.getMessage());
			System.out.println("Stack Trace al Insertar la Orden de Compra: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(connection);
		}
		return result;
	}

	private void insertarDetallesOrden(Connection connection, long ordenId, OrdenDeCompraDetalle ordenDetalle) throws SQLException {
		String query = "INSERT INTO orden_detalle (orden_id, material_id, cantidad, precio_unitario) values(?,?,?,?)";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1, ordenId);
		ps.setLong(2, ordenDetalle.getMaterial().getMaterialId());
		ps.setInt(3, ordenDetalle.getCantidad());
		ps.setFloat(4, ordenDetalle.getPrecioUnitario());
		ps.execute();
	}
		
	@Override
	public boolean modificar(OrdenDeCompra orden) {
		return false;
	}

	@Override
	public boolean eliminar(OrdenDeCompra orden) {
		return false;
	}

	@Override
	public OrdenDeCompra buscar(Long ordenId) {
		OrdenDeCompra orden = null;
		Connection connection = PoolConnection.getPoolConnection().getConnection();
		
		try{
			String query = "select * from orden_compra where orden_id = ?";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setLong(1, ordenId);
			ResultSet result = ps.executeQuery();
			
			if(result.next()) {
				Proveedor proveedor = ProveedorMapper.getInstance().buscar(result.getLong("proveedor_id"));
				orden = new OrdenDeCompra(result.getInt("orden_id"), result.getFloat("monto"), result.getDate("fecha"), proveedor);
				List<OrdenDeCompraDetalle> detalles = buscarDetallesOrden(connection, ordenId);
				orden.setDetalles(detalles);
			}
			
			PoolConnection.getPoolConnection().releaseConnection(connection);			
		} catch (SQLException e) {
			System.out.println("Mensaje Error al buscar Orden de Compra: " + e.getMessage());
			System.out.println("Stack Trace al buscar Orden de Compra: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(connection);
		}
		return orden;
	}

	@Override
	public List<OrdenDeCompra> buscarTodos() {
		List <OrdenDeCompra> ordenes = new ArrayList<OrdenDeCompra>();
		Connection connection = PoolConnection.getPoolConnection().getConnection();
		
		try {
			String query = "select * from orden_compra";
			PreparedStatement ps = connection.prepareStatement(query);
			
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Proveedor proveedor = ProveedorMapper.getInstance().buscar(rs.getLong("proveedor_id"));
				OrdenDeCompra orden = new OrdenDeCompra(rs.getInt("orden_id"), rs.getFloat("monto"), rs.getDate("fecha"), proveedor);
				List<OrdenDeCompraDetalle> detalles = buscarDetallesOrden(connection, orden.getOrdenId());
				orden.setDetalles(detalles);
				ordenes.add(orden);
			}
			
			PoolConnection.getPoolConnection().releaseConnection(connection);			
		} catch (SQLException e) {
			System.out.println("Mensaje Error Buscar todas las Ordenes: " + e.getMessage());
			System.out.println("Stack Trace al Buscar todas las Ordenes: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(connection);
		}
		
		return ordenes;
	}
		
	private List<OrdenDeCompraDetalle> buscarDetallesOrden(Connection connection, long ordenId){
		List<OrdenDeCompraDetalle> ordenDetalles = new ArrayList<OrdenDeCompraDetalle>();
		
		try {
			String query = "select * from orden_detalle where orden_id = ?";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setLong(1, ordenId);
			
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Material material = MaterialMapper.getInstance().buscar(rs.getLong("material_id"));
				OrdenDeCompraDetalle detalle = new OrdenDeCompraDetalle(material, rs.getInt("cantidad"), rs.getInt("precio_unitario"));
				ordenDetalles.add(detalle);
			}
			
			PoolConnection.getPoolConnection().releaseConnection(connection);			
		} catch (SQLException e) {
			System.out.println("Mensaje Error al buscar Detalles de la Orden: " + e.getMessage());
			System.out.println("Stack Trace al buscar Detalles de la Orden: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(connection);
		}
		
		return ordenDetalles;
	}
	

}
